import requests
import time
import hmac
import base64
import hashlib
import json
import frappe
import requests
import time
import hmac
import base64
import hashlib
import json
import os
import logging
from pathlib import Path
from datetime import datetime
import re


class YunExpressClient:
        # 全局缓存变量
    _global_pricing_data = {}
    _global_pricing_info = {}
    _global_tax_rates = {}
    _global_dimension_limits = {}
    _global_current_tariff_file = None
    _global_eff_date = None
    _global_version_no = None
    _global_data_loaded = False
    def __init__(self):
        #以下是沙箱环境
        self.host = "https://openapi-sbx.yunexpress.cn"
        self.appKey = "8ba596441ad3"
        self.appSecret = "382d15c5e78c4a1c9eb6219c0ec02246"
        self.sourceKey = "3tfly6uo"


        #以下是正式环境
        self.host = "https://openapi.yunexpress.cn"
        self.appKey = "edcf1674f704"
        self.appSecret = "048ce899bdcb4baaa89012b2a96592a9"
        self.sourceKey = "vzgy28lx"

        self.accessToken = ""
        self.expiresIn = 0
        self.session = requests.Session()

        self.tariff_dir = Path(__file__).parent / "tariff"
        self.tariff_dir.mkdir(exist_ok=True)
        
        # 使用全局缓存数据
        self.pricing_data = self._global_pricing_data
        self.pricing_info = self._global_pricing_info
        self.tax_rates = self._global_tax_rates
        self.dimension_limits = self._global_dimension_limits
        self.current_tariff_file = self._global_current_tariff_file
        self._eff_date = self._global_eff_date
        self._version_no = self._global_version_no
        
        # 如果数据未加载，则加载一次
        if not self._global_data_loaded:
            self.load_pricing_data()
            self._update_global_cache()

    def _canonicalize_body(self, body):
        """将请求体转换为云途签名要求的标准JSON字符串"""
        if body is None:
            return None

        if isinstance(body, (dict, list)):
            return json.dumps(body, ensure_ascii=False, separators=(',', ':'), sort_keys=True)

        if isinstance(body, bytes):
            return body.decode('utf-8')

        if isinstance(body, str):
            return body

        # 其他类型统一转为JSON字符串
        return json.dumps(body, ensure_ascii=False, separators=(',', ':'), sort_keys=True)

    def calculation_signature(self, content, secret):
        h = hmac.new(secret.encode('utf-8'), content.encode('utf-8'), digestmod=hashlib.sha256)
        return base64.b64encode(h.digest()).decode('utf-8')

    def get_headers(self, token, body, url, method):
        date = int(time.time() * 1000)
        body_str = ""
        body_json = ""
        if body:
            # 云途签名要求请求体使用无空白的JSON字符串，并按键名排序
            body_json = self._canonicalize_body(body)
            body_str = "body=" + body_json + "&"
        content = f"{body_str}date={date}&method={method}&uri={url}"
        sign = self.calculation_signature(content, self.appSecret)
        
        headers = {
            "Content-Type": "application/json",
            "token": str(token),
            "date": str(date),
            "Accept-Language": "zh-CN",
            "sign": sign,
        }
        return headers

    def get_access_token(self):
        if self.accessToken and time.time() * 1000 < self.expiresIn:
            return self.accessToken
        url = self.host + "/openapi/oauth2/token"
        data = {
            "grantType": "client_credentials",
            "appId": self.appKey,
            "appSecret": self.appSecret,
            "sourceKey": self.sourceKey,
        }
        try:
            resp = self.session.post(url, json=data)
            resp.raise_for_status()
            resp_data = resp.json()
            
            self.accessToken = resp_data.get('accessToken', '')
            self.expiresIn = int(resp_data.get('expiresIn', 0)) + int(time.time() * 1000)
            
            if not self.accessToken:
                raise Exception(f"云途Token获取失败: {resp_data}")
                
            return self.accessToken
        except Exception as e:
            frappe.logger().error(f"云途Token获取异常: {str(e)}")
            raise Exception(f"云途Token获取异常: {str(e)}")

    def _request(self, url, method, headers=None, params=None, data=None, files=None, timeout=30):
        try:
            full_url = self.host + url
            frappe.logger().debug(f"云途API请求: {method} {full_url}")
            
            if method.lower() == "get":
                r = self.session.get(full_url, headers=headers, params=params, timeout=timeout)
            elif method.lower() == "post":
                if files:
                    r = self.session.post(full_url, headers=headers, files=files, data=data, timeout=timeout)
                else:
                    canonical_body = self._canonicalize_body(data)
                    if canonical_body and isinstance(canonical_body, str):
                        canonical_body = canonical_body.encode("utf-8")
                    r = self.session.post(full_url, headers=headers, data=canonical_body, timeout=timeout)
            # 可扩展 put, delete
            
            try:
                r.raise_for_status()  # 检查HTTP状态码
            except requests.exceptions.HTTPError as http_err:
                response_text = None
                response_json = None
                if http_err.response is not None:
                    try:
                        response_text = http_err.response.text
                    except Exception:
                        response_text = None
                    try:
                        response_json = http_err.response.json()
                    except Exception:
                        response_json = None

                frappe.logger().error(
                    f"云途HTTP错误详情: status={getattr(http_err.response, 'status_code', 'unknown')} "
                    f"url={full_url} response_text={response_text} response_json={response_json}"
                )
                raise http_err

            response = r.json()
            
            # 检查业务错误码
            if not response.get('success', True):
                error_code = response.get('code', 'N/A')
                error_msg = response.get('msg', '未知错误')
                detailed_msg = f"云途API业务错误: {error_msg} (错误码: {error_code})"
                frappe.logger().error(detailed_msg)
                
                # 创建更友好的异常信息
                friendly_exception = Exception(f"云途物流API调用失败: {error_msg}")
                friendly_exception.error_code = error_code
                friendly_exception.original_message = error_msg
                raise friendly_exception
            
            frappe.logger().debug(f"云途API响应成功: {url}")
            return response
            
        except requests.exceptions.Timeout:
            error_msg = f"云途API请求超时: {url}"
            frappe.logger().error(error_msg)
            raise Exception(error_msg)
        except requests.exceptions.ConnectionError:
            error_msg = f"云途API连接错误: {url}"
            frappe.logger().error(error_msg)
            raise Exception(error_msg)
        except requests.exceptions.HTTPError as e:
            params_str = str(params) if params is not None else "None"
            data_str = str(data) if data is not None else "None"
            response_text = None
            response_json = None
            if e.response is not None:
                try:
                    response_text = e.response.text
                except Exception:
                    response_text = None
                try:
                    response_json = e.response.json()
                except Exception:
                    response_json = None

            error_msg = (
                f"云途API HTTP错误: {e} - {method} - {url} - {headers} - {params_str} - {data_str} "
                f"- response_text={response_text} - response_json={response_json}"
            )
            frappe.logger().error(error_msg)
            raise Exception(error_msg)
        except json.JSONDecodeError:
            error_msg = f"云途API响应JSON解析错误: {url}"
            frappe.logger().error(error_msg)
            raise Exception(error_msg)
        except Exception as e:
            error_msg = f"云途API请求异常: {str(e)} - {url}"
            frappe.logger().error(error_msg)
            raise Exception(error_msg)

    # 下面皆为 API方法，参数同 TS代码
    def get_country_code(self):
        """
        查询国家简码接口
        
        查询所有国家/地区二字简码参数信息，用于寄件、试算物流运费等场景。
        
        Returns:
            dict: 国家列表，包含以下字段：
                - t (str): 响应时间戳
                - success (boolean): 响应成功标识（true：成功，false：失败）
                - result (dict): 成功响应详情信息，包含：
                    - list (array): 国家列表，每个国家包含：
                        - country_name (str): 国家名称
                        - country_number (int): 国家编号
                        - country_code (str): 国家二字简码
                        - country_code_three (str): 国家三字简码
                        - international_code (str): 国际代码
                        
        Raises:
            Exception: 当API调用失败时
            
        Example:
            >>> countries = yt.get_country_code()
            >>> print(countries)
            {
                "t": "1677055488822",
                "success": true,
                "result": {
                    "list": [
                        {
                            "country_name": "安道尔",
                            "country_number": 20,
                            "country_code": "AD",
                            "country_code_three": "AND",
                            "international_code": ""
                        }
                    ]
                }
            }
        """
        token = self.get_access_token()
        url = "/v1/basic-data/countries/getlist"
        headers = self.get_headers(token, {}, url, "GET")
        try:
            result = self._request(url, "get", headers=headers)
            return result
        except Exception as e:
            raise e

    def get_logistics_product(self, country_code=None):
        """
        查询物流产品接口
        
        查询物流产品编码、名称等基础信息，用于寄件、轨迹订阅等场景。
        
        Args:
            country_code (str, optional): 国家/地区二字简码，未填写代表查询所有物流产品
                长度：0-2个字符
            
        Returns:
            dict: 物流产品列表，包含以下字段：
                - success (boolean): 响应成功标识（true：成功，false：失败）
                - t (str): 时间戳
                - msg (str): 结果描述
                - code (str): 异常响应错误码（成功响应时该字段为空）
                - detail (array): 成功响应详细信息，包含：
                    - product_code (str): 物流产品编码
                    - product_name (str): 物流产品名称
                    
        Raises:
            Exception: 当API调用失败时
            
        Example:
            >>> # 查询所有物流产品
            >>> products = yt.get_logistics_product()
            >>> 
            >>> # 查询美国可用物流产品
            >>> us_products = yt.get_logistics_product("US")
            >>> print(us_products)
            {
                "success": true,
                "t": "1677055618",
                "detail": [
                    {
                        "product_code": "BPA",
                        "product_name": "云途-B 小包平邮+"
                    }
                ]
            }
            
        Error Codes:
            - 02050002: 接口异常，请联系客服人员
            - 02050008: 参数无效，参数名 {0}
            - 02050024: 当前区域语言不支持
            - 02059250: 国家二字码不匹配
        """
        token = self.get_access_token()
        url = "/v1/basic-data/products/getlist"
        params = {}
        if country_code:
            params['countryCode'] = country_code
        headers = self.get_headers(token, {}, url, "GET")
        return self._request(url, "get", headers=headers, params=params)

    def register_ioss(self, body):
        token = self.get_access_token()
        url = "/v1/precondition-service/ioss/register"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    def disable_ioss(self, body):
        token = self.get_access_token()
        url = "/v1/precondition-service/ioss/disable"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    def register_vat(self, body):
        token = self.get_access_token()
        url = "/v1/precondition-service/vat/register"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    def calculate_logistics_fee(self, query):
        """
        试算物流运费接口
        
        根据目的国家/地区、包裹重量等信息模拟运算物流费用，此接口返回运价仅作参考，最终费用请以实际账单为准。
        
        Args:
            query (dict): 试算参数，包含以下字段：
                必要字段：
                - country_code (str): 目的国家/地区二字简码，长度2
                - weight (number): 试算包裹重量，数值范围0.001~1000.000
                
                可选字段：
                - weight_unit (str): 试算包裹重量单位，枚举：KG(千克)、G(克)、LBS(磅)，默认KG
                - package_type (str): 包裹类型，枚举：C(普货)、E(带电)、F(特货)，默认E
                - postal_code (str): 邮编，长度0~20
                - product_group_code (str): 物流产品组代码，枚举：DH(快速-快递)、EM(平邮-经济)、FB(FBA)、MA(标准-挂号)、PY(平邮-优先)、UP(标准-EUB)、SP(快速-专线)、A(空运)、RT(退件标准产品)、EP(深圳机场快运)
                - pieces (number): 件数，数值范围0~1000
                - length (number): 包裹长度，数值范围0-10000.000，默认1
                - width (number): 包裹宽度，数值范围0-10000.000，默认1
                - height (number): 包裹高度，数值范围0-10000.000，默认1
                - size_unit (str): 包裹尺寸单位，枚举：CM(厘米)、INCH(英寸)，默认CM
                - origin (str): 计费地点，长度1-20，默认YT-SZ
                
        Returns:
            dict: 试算结果，包含以下字段：
                - t (str): 响应时间戳
                - success (boolean): 响应成功标识（true：成功，false：失败）
                - result (array): 成功响应详细信息，包含：
                    - product_code (str): 物流产品编码
                    - product_name (str): 物流产品名称
                    - fee_name (str): 费用名称
                    - calculate_amount (number): 计算金额
                    - currency (str): 币种
                    - interval_day (str): 时效天数
                    - price_name (str): 价格名称
                    - price_type (str): 价格类型
                    - convert_currency (str): 转换币种
                    - rate (number): 汇率
                    - convert_amount (number): 转换金额
                - msg (str): 请求响应描述
                - code (str): 异常响应错误码
                
        Raises:
            ValueError: 当必要参数缺失或格式不正确时
            Exception: 当API调用失败时
            
        Example:
            >>> fee_query = {
            ...     "country_code": "US",
            ...     "weight": 1.5,
            ...     "weight_unit": "KG",
            ...     "package_type": "E"
            ... }
            >>> result = yt.calculate_logistics_fee(fee_query)
            >>> print(result)
            {
                "t": "1679367266860",
                "success": true,
                "result": [
                    {
                        "product_code": "BKZXR",
                        "product_name": "云途全球专线挂号（标快）",
                        "fee_name": "E1",
                        "calculate_amount": 8,
                        "currency": "RMB",
                        "interval_day": "3-8"
                    }
                ]
            }
            
        Error Codes:
            - 02060000: 系统异常，请稍后重试！
            - 02060001: 重量不能为空！
            - 02060002: 目的国家不能为空！
            - 02060003: 暂不支持该包裹类型！
            - 02060004-02060016: 非常抱歉，未能推荐合适的价格给您，请联系客服人员
        """
        # 参数验证
        if not query.get('country_code'):
            raise ValueError("目的国家不能为空")
            
        if not query.get('weight'):
            raise ValueError("重量不能为空")
            
        country_code = query['country_code']
        if len(country_code) != 2:
            raise ValueError("目的国家/地区二字简码长度必须为2")
            
        weight = query['weight']
        if not isinstance(weight, (int, float)) or weight <= 0 or weight > 1000:
            raise ValueError("重量值范围为0.001~1000.000")
        
        token = self.get_access_token()
        url = "/v1/price-trial/get"
        headers = self.get_headers(token, {}, url, "GET")
        return self._request(url, "get", headers=headers, params=query)

    def get_order_fee_detail(self, query):
        token = self.get_access_token()
        url = "/v1/order/fee-details/get"
        headers = self.get_headers(token, {}, url, "GET")
        return self._request(url, "get", headers=headers, params=query)

    def get_bill_detail_list(self, query):
        token = self.get_access_token()
        url = "/v1/bill/details/list"
        headers = self.get_headers(token, {}, url, "GET")
        return self._request(url, "get", headers=headers, params=query)

    def get_order_track(self, order_number):
        """
        查询运单轨迹信息
        
        Args:
            order_number (str): 单号（支持运单号、客户单号、跟踪号）
            
        Returns:
            dict: 轨迹信息
        """
        if not order_number:
            raise ValueError("单号不能为空")
            
        token = self.get_access_token()
        url = "/v1/track-service/info/get"
        params = {'order_number': order_number}
        headers = self.get_headers(token, {}, url, "GET")
        return self._request(url, "get", headers=headers, params=params)

    def single_ticket_send(self, body):
        """
        单票寄件接口
        
        客户端向云途订单系统发起订单预报申请，用于单票寄件场景。
        
        Args:
            body (dict): 订单数据，包含以下字段：
                必要字段：
                - product_code (str): 物流产品编码，长度1-50
                - receiver (dict): 订单收件人信息
                - packages (array): 订单包裹信息，最小数量1
                - declaration_info (array): 申报信息，最小数量1
                
                可选字段：
                - customer_order_number (str): 客户唯一订单号，长度0-50
                - extra_services (array): 订单附加服务
                - order_numbers (dict): 订单号码集合
                - sender (dict): 订单发件人
                - customs_number (dict): 清关税号
                - platform_account_code (str): 平台子帐号代码，长度0-20
                - source_code (str): 订单来源代码，长度0-10
                - sensitive_type (str): 特殊货品类型，枚举：W(包裹)、D(文件)、F(分单)、L(信封)
                - label_type (str): 期望的标签类型，枚举：PDF、ZPL、PNG
                - weight_unit (str): 包裹重量单位，枚举：KG(千克)、G(克)、LBS(磅)，默认KG
                - size_unit (str): 包裹尺寸单位，枚举：CM(厘米)、INCH(英寸)，默认CM
                - dangerous_goods_type (str): 危险品类型，长度0-30
                - platform (dict): 平台信息
                - payment (dict): 支付信息
                - point_relais_num (str): 自提点编码，最大长度1000
                
        Returns:
            dict: 创建结果，包含以下字段：
                - t (str): 响应时间戳
                - success (boolean): 响应成功标识（true：成功，false：失败）
                - result (dict): 成功响应详情信息，包含：
                    - customer_order_number (str): 客户订单号
                    - track_type (str): 跟踪类型
                    - waybill_number (str): 运单号
                    - tracking_number (str): 跟踪号
                    - bar_codes (str): 条码
                    - remote_area (str): 偏远地区标识
                    - shipper_boxs (array): 发货箱信息
                - msg (str): 请求响应描述（成功响应时该字段为空）
                - code (str): 异常响应错误码（成功响应时该字段为空）
                
        Raises:
            ValueError: 当必要参数缺失时
            Exception: 当API调用失败时
            
        Example:
            >>> order_data = {
            ...     "product_code": "THPHR",
            ...     "customer_order_number": "ORDER123",
            ...     "receiver": {
            ...         "first_name": "John",
            ...         "last_name": "Doe",
            ...         "country_code": "US",
            ...         "city": "Los Angeles",
            ...         "address_lines": ["123 Main St"],
            ...         "postal_code": "90210",
            ...         "phone_number": "1234567890"
            ...     },
            ...     "packages": [{
            ...         "length": 10.0,
            ...         "width": 8.0,
            ...         "height": 5.0,
            ...         "weight": 1.5
            ...     }],
            ...     "declaration_info": [{
            ...         "sku_code": "SKU001",
            ...         "name_local": "测试商品",
            ...         "name_en": "Test Product",
            ...         "quantity": 1,
            ...         "unit_price": 10.0,
            ...         "unit_weight": 1.5,
            ...         "hs_code": "1234567890"
            ...     }]
            ... }
            >>> result = yt.single_ticket_send(order_data)
            >>> print(f"运单号: {result['result']['waybill_number']}")
            
        Error Codes:
            - 02030001: 接口异常，请稍后重试或联系客服人员
            - 02030006: 国家不存在
            - 02030008: 参数无效，请参考接口文档
            - 02030010: 收货人邮编只能输入字母，数字和空格
            - 02030021: 未匹配到自提点
            - 02030024: 所选产品为自提产品,自提点编码必填
            - 02030025: 无效的自提点编码
            - 02039006: 接口异常，订单不存在
            - 02039007: 产品无服务或重量不支持
            - 02039008: 目标国家不可达
            - 02039009: 下单规则校验失败
            - 02039014: 请联系您的销售经理，完成实名认证
            - 02039015: 数据提交中，请勿重复提交
            - 02039019: 当前选择的物流产品不存在或不可用
            - 02039021: 客户订单号不能包含汉字
            - 02039026: 收件人信息必填
            - 02039039: 收件人信息不能包含特殊字符
            - 02039042: FBA订单的申报信息，SKU不能重复
            - 02039044: 箱号不允许重复
            - 02039058: 接口异常，请联系客服人员
            - 02039059: 接口异常，请联系客服人员
            - 02039063: 客户单号为空
            - 02039064: 运单号为空
            - 02039065: 运单号重复
            - 02039066: 订单号重复
            - 02039067: 跟踪号重复
            - 02039068: 无效运单号
            - 02039069: 接口异常，请联系客服人员
            - 02039070: 接口异常，请联系客服人员
            - 02039081: 接口异常，请联系客服人员
            - 02039082: 获取SKU备案信息失败
            - 02039083: 目标地址（邮编）不可达或重量不支持
            - 02039084: 接口异常，请稍后重试或联系客服人员
            - 02039085: 接口异常，请联系客服人员
            - 02039086: 接口异常，请联系客服人员
            - 02039087: 本线路不服务Amazon，Fba地址
            - 02039088: 产品下单限制-超过限量
            - 02039089: 产品下单限制-物流产品不存在
            - 02039090: 产品下单限制-客户代码或物流产品都为空
            - 02039091: 接口异常，请稍后重试或联系客服人员
            - 02039092: 如需使用此产品进行发货，请联系您的销售经理
            - 02039093: 接口异常，请稍后重试或联系客服人员
            - 02039096: 接口异常，请稍后重试或联系客服人员
            - 02039100: 收件人邮箱不符合规则
            - 02039101: 收件人地址未填写税号或税号格式不正确
            - 02039102: 客户订单号不能是YT、WS、JM开头加16位数字
            - 02039116: 接口异常，请联系客服人员
            - 02039118: 接口异常，请联系客服人员
            - 02039120: 接口异常，请联系客服人员
            - 02039121: 中文品名含敏感词
            - 02039122: 英文品名含敏感词
            - 02039123: 请联系您的销售经理，完成合同签署
            - 02039127: 接口异常，请联系客服人员
            - 02039143: 申报信息币种不统一
            - 02039147: 非已预报或已入仓状态不能拦截或取消拦截
            - 02039152: 地址不正确，地址与城市或省州重复
            - 02039154: 请联系您的销售经理，绑定签约主体
            - 02039155: 订单号长度需在6-50位，不允许有特殊字符
            - 02039159: FBA订单子单数量不能超过500个
            - 02039160: 未获取到公共发件人信息
            - 02039161: 需要分配公共发件人，地址返回空
            - 02039162: 可到国家验证
            - 02039163: 地址验证
            - 02039164: 该销售产品仅API可下单
            - 02039165: 请联系客服人员
            - 02039166: 发往美国亚马逊Amazon的申报价值≤800.00USD，此渠道暂不支持
            - 02039169: 您下单的物流产品不存在，请联系您的销售经理
            - 02039170: 目标国家不可达
            - 02039171: 下单规则校验失败
            - 02039179: IOSS号未备案，请先备案
            - 02039181: 默认的IOSS号未备案，请先备案
            - 02039183: 该物流产品不支持网站下单，请联系客服人员
            - 02039189: 物流产品配置错误，请联系客服人员
            - 02039190: VAT号未备案，请先备案
            - 02039192: 未同意云途代缴协议，请同意此协议后再下单
            - 02039193: 运单申请失败
            - 02039197: IOSS号不正确，请使用正确的IOSS号重新提交备案
            - 02039202: VAT号不正确，请使用正确的VAT号重新提交备案
            - 02039209: 重量单位只支持KG/G/LBS
            - 02039242: 揽收国家二字简码填写有误
            - 02039244: 禁止下单，请联系客服人员
            - 02039249: 仓库代码不匹配
            - 02039250: 收件人国家二字码不匹配
            - 02039256: 收件人国家二字码无效
            - 02039258: 客户账号状态异常，请联系您的销售经理
            - 02039260: 外包装数（货物件数）和箱子总数不相等
            - 02039262: 箱号的ReferenceID需必填
            - 02039263: 箱号填写格式不正确，FBA箱号只能为数字或字母且长度最少12位
            - 02039265: 前12位字符一致的箱号，ReferenceID需要完全相同
            - 02039274: 当前销售产品，仅支持对应平台下单
            - 02039276: 长度单位不符合规则
            - 02039306: 重量必须大于0且最多3位小数
            - 02039311: 下单失败，账户余额不足，请先充值
        """
        # 参数验证
        required_fields = ['product_code', 'receiver', 'packages', 'declaration_info']
        for field in required_fields:
            if field not in body:
                raise ValueError(f"缺少必要参数: {field}")
        
        frappe.logger().debug("=== 进入single_ticket_send方法 ===")
        frappe.logger().debug("=== 开始获取access token ===")
        token = self.get_access_token()
        frappe.logger().debug(f"=== 获取到token: {token[:50]}... ===")
        url = "/v1/order/package/create"
        frappe.logger().debug("=== 开始构建请求头 ===")
        headers = self.get_headers(token, body, url, "POST")
        
        frappe.logger().debug(f"云途POST请求头: {headers}")
        frappe.logger().info(f"云途POST请求URL: {self.host + url}")
        frappe.logger().debug(f"云途POST请求数据: {json.dumps(body, ensure_ascii=False, indent=2)}")
        
        # 添加更详细的错误处理
        try:
            result = self._request(url, "post", headers=headers, data=body)
            frappe.logger().info(f"云途POST请求成功，响应: {result}")
            return result
        except Exception as e:
            frappe.logger().error(f"云途POST请求失败: {str(e)}")
            # 检查是否是401错误
            if "401" in str(e):
                frappe.logger().error("云途API返回401错误，可能是认证问题，请检查appKey、appSecret和sourceKey是否正确")
            raise e

    def update_order_weight(self, body):
        """
        修改运单预报重量接口
        
        客户端向云途订单系统发起运单预报重量修改申请，仅支持对已预报或草稿状态下的运单进行重量修改。
        
        Args:
            body (dict): 修改重量请求参数，包含以下字段：
                - waybill_number (str): 运单号，长度1-50
                - weight (number): 重量，数值范围0.001-10000.000
                - weight_unit (str, optional): 重量单位，枚举值：KG(千克)、G(克)、LBS(磅)，默认KG
                
        Returns:
            dict: 修改结果，包含以下字段：
                - t (str): 响应时间戳
                - success (boolean): 响应成功标识（true：成功，false：失败）
                - result (object): 成功响应详情信息（异常响应时该字段为空）
                - msg (string): 请求响应描述（成功响应时该字段为空）
                - code (string): 异常响应错误码（成功响应时该字段为空）
                
        Raises:
            ValueError: 当必要参数缺失或格式不正确时
            Exception: 当API调用失败时
            
        Example:
            >>> weight_data = {
            ...     "waybill_number": "YT2230422116000027",
            ...     "weight": 1.5,
            ...     "weight_unit": "KG"
            ... }
            >>> result = yt.update_order_weight(weight_data)
            >>> print(result)
            {
                "t": "1678256420",
                "success": true,
                "result": null
            }
            
        Error Codes:
            - 02034413: 运单号不能为空
            - 02034414: 运单号长度为1到50个字符
            - 02034415: 重量不能为空
            - 02034416: 重量值范围为0.001-10000.000
            - 02034417: 重量单位只支持千克、克、磅
            - 02039006: 订单不存在
            - 02039084: 接口异常，请稍后重试或联系客服人员
            - 02039306: 重量必须大于0且不超过三位小数
            - 02039307: 订单不是已提交状态
        """
        # 参数验证
        if not body.get('waybill_number'):
            raise ValueError("运单号不能为空")
        
        if not body.get('weight'):
            raise ValueError("重量不能为空")
            
        weight = body['weight']
        if not isinstance(weight, (int, float)) or weight <= 0 or weight > 10000:
            raise ValueError("重量值范围为0.001-10000.000")
            
        waybill_number = body['waybill_number']
        if len(waybill_number) < 1 or len(waybill_number) > 50:
            raise ValueError("运单号长度为1到50个字符")
            
        weight_unit = body.get('weight_unit', 'KG')
        if weight_unit not in ['KG', 'G', 'LBS']:
            raise ValueError("重量单位只支持KG(千克)、G(克)、LBS(磅)")
        
        token = self.get_access_token()
        url = "/v1/order/weight/modify"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    def cancel_order(self, body):
        """
        撤销运单接口
        
        客户端向云途订单系统发起运单撤销申请，仅支持对已预报或草稿状态下的运单进行撤销。
        
        Args:
            body (dict): 撤销请求参数，包含以下字段：
                - waybill_number (str): 运单号，长度1-50
                
        Returns:
            dict: 撤销结果，包含以下字段：
                - t (str): 响应时间戳
                - success (boolean): 响应成功标识（true：成功，false：失败）
                - result (object): 成功响应详情信息（异常响应时该字段为空）
                - msg (str): 请求响应描述（成功响应时该字段为空）
                - code (str): 异常响应错误码（成功响应时该字段为空）
                
        Raises:
            ValueError: 当必要参数缺失或格式不正确时
            Exception: 当API调用失败时
            
        Example:
            >>> cancel_data = {
            ...     "waybill_number": "YT2230422116000027"
            ... }
            >>> result = yt.cancel_order(cancel_data)
            >>> print(result)
            {
                "t": "1678256420",
                "success": true,
                "result": null
            }
            
        Error Codes:
            - 02034413: 运单号不能为空
            - 02034414: 运单号长度为1到50个字符
            - 02039006: 订单不存在
            - 02039084: 接口异常，请稍后重试或联系客服人员
        """
        # 参数验证
        if not body.get('waybill_number'):
            raise ValueError("运单号不能为空")
            
        waybill_number = body['waybill_number']
        if len(waybill_number) < 1 or len(waybill_number) > 50:
            raise ValueError("运单号长度为1到50个字符")
        
        token = self.get_access_token()
        url = "/v1/order/cancel"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    def intercept_order(self, body):
        """
        拦截运单接口
        
        客户端向云途订单系统发起运单拦截申请，可通过此接口对运输中的包裹进行拦截处理。
        
        Args:
            body (dict): 拦截请求参数，包含以下字段：
                - waybill_number (str): 运单号，长度1-50
                - remark (str): 备注，长度1-100
                
        Returns:
            dict: 拦截结果，包含以下字段：
                - t (str): 响应时间戳
                - success (boolean): 响应成功标识（true：成功，false：失败）
                - result (object): 成功响应详情信息（异常响应时该字段为空）
                - masg (str): 请求响应描述（成功响应时该字段为空）
                - code (str): 异常响应错误码（成功响应时该字段为空）
                
        Raises:
            ValueError: 当必要参数缺失或格式不正确时
            Exception: 当API调用失败时
            
        Example:
            >>> intercept_data = {
            ...     "waybill_number": "YT2231431267000001",
            ...     "remark": "客户暂扣"
            ... }
            >>> result = yt.intercept_order(intercept_data)
            >>> print(result)
            {
                "t": "1678256929",
                "success": true,
                "result": null
            }
            
        Error Codes:
            - 02034413: 运单号不能为空
            - 02034414: 运单号长度为1到50个字符
            - 02034418: 描述不能为空
            - 02034419: 描述长度为1到200个字符
            - 02039006: 订单不存在
            - 02039084: 接口异常，请稍后重试或联系客服人员
        """
        # 参数验证
        if not body.get('waybill_number'):
            raise ValueError("运单号不能为空")
            
        if not body.get('remark'):
            raise ValueError("备注不能为空")
            
        waybill_number = body['waybill_number']
        if len(waybill_number) < 1 or len(waybill_number) > 50:
            raise ValueError("运单号长度为1到50个字符")
            
        remark = body['remark']
        if len(remark) < 1 or len(remark) > 100:
            raise ValueError("备注长度为1到100个字符")
        
        token = self.get_access_token()
        url = "/v1/order/hold"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    def get_order_label(self, query):
        token = self.get_access_token()
        url = "/v1/order/label/get"
        headers = self.get_headers(token, {}, url, "GET")
        return self._request(url, "get", headers=headers, params=query)

    def get_delivery_proof(self, query):
        token = self.get_access_token()
        url = "/v1/order/shipping-docs/get"
        headers = self.get_headers(token, {}, url, "GET")
        return self._request(url, "get", headers=headers, params=query)

    def get_sign_proof(self, query):
        token = self.get_access_token()
        url = "/v1/order/pod/get"
        headers = self.get_headers(token, {}, url, "GET")
        return self._request(url, "get", headers=headers, params=query)

    def get_order_detail(self, query):
        token = self.get_access_token()
        url = "/v1/order/info/get"
        headers = self.get_headers(token, {}, url, "GET")
        return self._request(url, "get", headers=headers, params=query)

    def get_sender(self, query):
        token = self.get_access_token()
        url = "/v1/order/sender/get"
        headers = self.get_headers(token, {}, url, "GET")
        return self._request(url, "get", headers=headers, params=query)

    def get_order_end_delivery(self, query):
        token = self.get_access_token()
        url = "/v1/order/last-mile/get"
        headers = self.get_headers(token, {}, url, "GET")
        return self._request(url, "get", headers=headers, params=query)

    def subscribe_order_track(self, body):
        token = self.get_access_token()
        url = "/v1/track-service/subscribe-by-order"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    def unsubscribe_order_track(self, body):
        token = self.get_access_token()
        url = "/v1/track-service/unsubscribe-by-order"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    def get_subscribe_order_track(self, query):
        token = self.get_access_token()
        url = "/v1/track-service/subscribe-by-order/get"
        headers = self.get_headers(token, {}, url, "GET")
        return self._request(url, "get", headers=headers, params=query)

    def subscribe_logistics_product_track(self, body):
        token = self.get_access_token()
        url = "/v1/track-service/subscribe-by-shipping"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    def unsubscribe_logistics_product_track(self, body):
        token = self.get_access_token()
        url = "/v1/track-service/unsubscribe-by-shipping"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    def get_subscribe_logistics_product_track(self, query):
        token = self.get_access_token()
        url = "/v1/track-service/subscribe-by-shipping/get"
        headers = self.get_headers(token, {}, url, "GET")
        return self._request(url, "get", headers=headers, params=query)

    def upload_file(self, file_path):
        token = self.get_access_token()
        # 文件上传使用不同的域名
        file_host = "https://openapi-file-sbx.yunexpress.cn"
        url = "/v1/file/upload"
        headers = self.get_headers(token, {}, url, "POST")
        headers["Content-Type"] = "multipart/form-data"
        files = {'file': open(file_path, 'rb')}
        full_url = file_host + url
        r = self.session.post(full_url, headers=headers, files=files)
        return r.json()

    def get_customer_receive_address(self, query):
        token = self.get_access_token()
        url = "/v1/issue/get-receive-address"
        headers = self.get_headers(token, {}, url, "GET")
        return self._request(url, "get", headers=headers, params=query)

    def get_quality_control_exception_optional_solution(self, query):
        token = self.get_access_token()
        url = "/v1/issue/get-options"
        headers = self.get_headers(token, {}, url, "GET")
        return self._request(url, "get", headers=headers, params=query)

    def pass_quality_control_exception(self, body):
        token = self.get_access_token()
        url = "/v1/issue/release"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    def specify_quality_control_exception(self, body):
        token = self.get_access_token()
        url = "/v1/issue/handle"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    def mark_quality_control_exception_read(self, body):
        token = self.get_access_token()
        url = "/v1/issue/read"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    def declare_quality_control_exception(self, body):
        token = self.get_access_token()
        url = "/v1/issue/feedback"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    def add_value_quality_control_exception(self, body):
        token = self.get_access_token()
        url = "/v1/issue/warehouse-process"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    def exchange_quality_control_exception(self, body):
        token = self.get_access_token()
        url = "/v1/issue/change-waybill-number"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    def return_quality_control_exception(self, body):
        token = self.get_access_token()
        url = "/v1/issue/return-supply"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    def reforecast_quality_control_exception(self, body):
        token = self.get_access_token()
        url = "/v1/issue/re-forecast"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    def reassign_quality_control_exception(self, body):
        token = self.get_access_token()
        url = "/v1/issue/retry-delivery"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    def select_quality_control_exception_optional_solution(self, body):
        token = self.get_access_token()
        url = "/v1/issue/select-solution"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    def customer_feedback_quality_control_exception(self, body):
        token = self.get_access_token()
        url = "/v1/issue/customer-feedback"
        headers = self.get_headers(token, body, url, "POST")
        return self._request(url, "post", headers=headers, data=body)

    # ==================== 测试和示例方法 ====================
    
    def test_connection(self):
        """
        测试云途API连接
        
        Returns:
            dict: 测试结果
        """
        try:
            result = self.get_country_code()
            return {
                "success": True,
                "message": "云途API连接正常",
                "data": result
            }
        except Exception as e:
            return {
                "success": False,
                "message": f"云途API连接失败: {str(e)}",
                "data": None
            }
    
    def get_api_status(self):
        """
        获取API状态信息
        
        Returns:
            dict: API状态信息
        """
        return {
            "host": self.host,
            "app_key": self.appKey,
            "has_token": bool(self.accessToken),
            "token_expires": self.expiresIn,
            "current_time": int(time.time() * 1000)
        }

    def _load_from_pkl_files(self):
        """从pkl文件加载数据"""
        import pickle
        
        try:
            # 检查pkl文件是否存在
            pricing_data_path = self.tariff_dir / 'pricing_data.pkl'
            pricing_info_path = self.tariff_dir / 'pricing_info.pkl'
            tax_rates_path = self.tariff_dir / 'tax_rates.pkl'
            dimension_limits_path = self.tariff_dir / 'dimension_limits.pkl'
            
            if not all([pricing_data_path.exists(), pricing_info_path.exists(), 
                       tax_rates_path.exists(), dimension_limits_path.exists()]):
                print("调试: 部分pkl文件不存在，无法加载")
                return False
            
            # 加载产品信息
            print("正在从pkl文件加载产品信息...")
            with open(pricing_data_path, 'rb') as f:
                self.pricing_data = pickle.load(f)
            
            # 加载计费信息
            print("正在从pkl文件加载计费信息...")
            with open(pricing_info_path, 'rb') as f:
                self.pricing_info = pickle.load(f)
            
            # 加载税率信息
            print("正在从pkl文件加载税率信息...")
            with open(tax_rates_path, 'rb') as f:
                self.tax_rates = pickle.load(f)
            
            # 加载尺寸限制信息
            print("正在从pkl文件加载尺寸限制信息...")
            with open(dimension_limits_path, 'rb') as f:
                self.dimension_limits = pickle.load(f)
            
            print("从pkl文件加载数据成功")
            return True
            
        except Exception as e:
            print(f"从pkl文件加载数据失败: {str(e)}")
            return False

    def _update_global_cache(self):
        """更新全局缓存"""
        self._global_pricing_data = self.pricing_data
        self._global_pricing_info = self.pricing_info
        self._global_tax_rates = self.tax_rates
        self._global_dimension_limits = self.dimension_limits
        self._global_current_tariff_file = self.current_tariff_file
        self._global_eff_date = self._eff_date
        self._global_version_no = self._version_no
        self._global_data_loaded = True
        print("信息: 数据已缓存到全局变量")
    
    @classmethod
    def clear_global_cache(cls):
        """清空全局缓存"""
        cls._global_pricing_data = {}
        cls._global_tax_rates = {}
        cls._global_dimension_limits = {}
        cls._global_current_tariff_file = None
        cls._global_eff_date = None
        cls._global_version_no = None
        cls._global_data_loaded = False
        print("信息: 全局缓存已清空")
    
    @classmethod
    def is_global_cache_loaded(cls):
        """检查全局缓存是否已加载"""
        return cls._global_data_loaded
    
    @classmethod
    def get_global_cache_info(cls):
        """获取全局缓存信息"""
        return {
            'loaded': cls._global_data_loaded,
            'pricing_data_count': len(cls._global_pricing_data),
            'tax_rates_count': len(cls._global_tax_rates),
            'dimension_limits_count': len(cls._global_dimension_limits),
            'current_file': cls._global_current_tariff_file['file_name'] if cls._global_current_tariff_file else None,
            'effective_date': cls._global_eff_date,
            'version': cls._global_version_no
        }
    
    def load_pricing_data(self):
        """加载tariff目录下的最新有效Excel报价文件"""
        try:
            # 获取所有Excel文件
            excel_files = list(self.tariff_dir.glob("*.xlsx")) + list(self.tariff_dir.glob("*.xls"))
            
            # 筛选有效的CN开头文件
            valid_files = self._filter_valid_tariff_files(excel_files)
            
            if not valid_files:
                print("警告: 未找到有效的CN开头资费文件")
                return
            
            # 选择最新的文件
            latest_file = self._select_latest_tariff_file(valid_files)
            
            if latest_file:
                self.current_tariff_file = latest_file
                # 设置私有变量
                self._eff_date = latest_file['effective_date_str']
                self._version_no = latest_file['version']
                print(f"信息: 选择最新资费文件: {latest_file['file_name']} (生效时间: {latest_file['effective_date']}, 版本: {latest_file['version']})")
                self.parse_excel_file(latest_file['file_path'])
            else:
                print("错误: 无法确定最新的资费文件")
            
        except Exception as e:
            print(f"错误: 加载报价文件失败: {str(e)}")
    
    def _filter_valid_tariff_files(self, excel_files):
        """筛选有效的CN开头资费文件"""
        valid_files = []
        
        for file_path in excel_files:
            file_name = file_path.stem  # 不包含扩展名的文件名
            
            # 检查是否符合CN开头格式
            if self._is_valid_tariff_filename(file_name):
                file_info = self._parse_tariff_filename(file_name, file_path)
                if file_info:
                    valid_files.append(file_info)
                    print(f"信息: 发现有效资费文件: {file_name}")
            else:
                print(f"调试: 跳过无效文件: {file_name}")
        
        return valid_files
    
    def _is_valid_tariff_filename(self, filename):
        """检查文件名是否符合CN开头格式"""
        # 格式: CN0934765整合渠道报价产品20250901-7809333
        # CN开头，包含两个-分割的部分
        pattern = r'^CN\d+.*-\d+.*$'
        return bool(re.match(pattern, filename))
    
    def _parse_tariff_filename(self, filename, file_path):
        """解析资费文件名，提取生效时间和版本号"""
        try:
            # 按-分割文件名
            parts = filename.split('-')
            
            if len(parts) < 2:
                print(f"警告: 文件名格式不正确: {filename}")
                return None
            
            # 第一部分: CN0934765整合渠道报价产品20250901
            # 从中提取日期部分 (假设日期在最后8位)
            first_part = parts[0]
            date_match = re.search(r'(\d{8})$', first_part)
            
            if not date_match:
                print(f"警告: 无法从文件名提取日期: {filename}")
                return None
            
            effective_date_str = date_match.group(1)
            
            # 解析日期
            try:
                effective_date = datetime.strptime(effective_date_str, '%Y%m%d')
            except ValueError:
                print(f"警告: 日期格式不正确: {effective_date_str}")
                return None
            
            # 第二部分: 版本号 (如 7809333)
            version = parts[1] if len(parts) > 1 else "unknown"
            
            # 没有第三部分了
            additional_info = ""
            
            return {
                'file_name': filename,
                'file_path': file_path,
                'effective_date': effective_date,
                'effective_date_str': effective_date_str,
                'version': version,
                'additional_info': additional_info,
                'full_name': file_path.name
            }
            
        except Exception as e:
            print(f"错误: 解析文件名失败 {filename}: {str(e)}")
            return None
    
    def _select_latest_tariff_file(self, valid_files):
        """选择最新的资费文件（生效时间最接近现在的）"""
        if not valid_files:
            return None
        
        # 按生效时间排序，选择最新的

        latest_file = max(valid_files, key=lambda x: x['effective_date'])
        
        # 记录所有文件的信息
        print("信息: 发现的有效资费文件:")
        for file_info in sorted(valid_files, key=lambda x: x['effective_date'], reverse=True):
            print(f"  - {file_info['file_name']} (生效: {file_info['effective_date_str']}, 版本: {file_info['version']})")
        
        return latest_file
    
    def parse_excel_file(self, file_path):
        """解析单个Excel文件"""
        try:
            # 读取Excel文件的所有工作表，不自动处理表头
            excel_data = pd.read_excel(file_path, sheet_name=None, header=None)
           
            # 获取工作表列表
            sheet_names = list(excel_data.keys())
            if not sheet_names:
                print("错误: Excel文件中没有工作表")
                return
            
            # 只解析第一个工作表 (sheet[0]) 来获取产品信息
            first_sheet_name = sheet_names[0]
            first_sheet_data = excel_data[first_sheet_name]
            
            print(f"信息: 解析第一个工作表: {first_sheet_name}")
            
            # 清理第一个工作表数据
            first_sheet_data = first_sheet_data.dropna(how='all').dropna(axis=1, how='all')
            
            # 从第一个工作表提取产品信息
            valid_products = self._extract_products_from_first_sheet(first_sheet_data)
            print(f"信息: 从第一个工作表提取到 {len(valid_products)} 个有效产品")
            
            # 存储第一个工作表数据
            file_key = file_path.stem
            sheet_key = f"{file_key}_{first_sheet_name}"
            self.pricing_data[sheet_key] = {
                'file_name': file_path.name,
                'sheet_name': first_sheet_name,
                'data': first_sheet_data.to_dict('records'),
                'columns': first_sheet_data.columns.tolist(),
                'shape': first_sheet_data.shape,
                'valid_products': valid_products
            }
            
            # 解析其他工作表，但只处理在第一个工作表中存在的产品
            for i, (sheet_name, df) in enumerate(excel_data.items()):
                if i == 0:  # 跳过第一个工作表，已经处理过了
                    continue
                
                # 检查这个工作表是否对应有效产品
                matched_product = self._find_matching_product(sheet_name, valid_products)
                if matched_product:
                    print(f"信息: 解析价格工作表: {sheet_name} -> {matched_product['product_name']} ({matched_product['shipping_code']})")
                    
                    # 清理数据
                    df = df.dropna(how='all').dropna(axis=1, how='all')
                    
                    # 存储数据
                    sheet_key = f"{file_key}_{sheet_name}"
                    self.pricing_data[sheet_key] = {
                        'file_name': file_path.name,
                        'sheet_name': sheet_name,
                        'data': df.to_dict('records'),
                        'columns': df.columns.tolist(),
                        'shape': df.shape,
                        'matched_product': matched_product
                    }
                    
                    print(f"信息: 工作表 {sheet_name} 包含 {df.shape[0]} 行 {df.shape[1]} 列数据")
                    
                    # 解析价格信息
                    pricing_info = self._parse_pricing_sheet(df, matched_product)
                    self.pricing_data[sheet_key]['pricing_info'] = pricing_info
                    
                    print(f"信息: 解析到 {len(pricing_info)} 条计费信息")
                else:
                    print(f"调试: 跳过无效工作表: {sheet_name} (未找到匹配的产品)")
            
            # 解析税率表
            self._parse_tax_rates_table(excel_data)
            
            # 解析尺寸限制表
            self._parse_dimension_limits_table(excel_data)
                
        except Exception as e:
            print(f"错误: 解析Excel文件 {file_path} 失败: {str(e)}")
    
    def _extract_products_from_first_sheet(self, df):
        """从第一个工作表提取产品信息，返回以product_name为主键的map结构"""
        valid_products = {}
        
        try:
            # 从第6行第2列开始读取，寻找表头
            start_row = 0  # 第6行，索引为5
            start_col = 0  # 第2列，索引为1
            
            product_name_col = None
            shipping_code_col = None
            first_category_col = None
            second_category_col = None
            header_row = None
            
            print(f"调试: 开始从第{start_row+1}行第{start_col+1}列扫描表头...")
            print(f"调试: 数据框形状: {df.shape}")
            
            # 从第6行第2列开始扫描，寻找表头
            for row_idx in range(start_row, min(start_row + 10, len(df))):  # 最多扫描10行
                print(f"调试: 扫描第{row_idx+1}行...")
                
                # 先寻找一级分类
                first_category_col = None
                for col_idx in range(start_col, len(df.columns)):
                    cell_value = str(df.iloc[row_idx, col_idx]).strip()
                    if cell_value == 'nan' or not cell_value:
                        continue
                    
                    if '一级分类' in cell_value:
                        first_category_col = col_idx
                        header_row = row_idx
                        break
                
                # 如果找到一级分类，验证后续列是否为完整的表头
                if first_category_col is not None:
                    print(f"调试: 验证第{row_idx+1}行的表头完整性...")
                    
                    # 验证表头：一级分类 -> 二级分类 -> 产品名称 -> 运输代码 -> 报价表 -> 走货属性 -> 优势推荐 -> 更新时间
                    expected_headers = ['一级分类', '二级分类', '产品名称', '运输代码', '报价表', '走货属性', '优势推荐', '更新时间']
                    found_headers = []
                    header_cols = {}
                    
                    # 从一级分类列开始，扫描后续列，寻找所有可能的表头
                    for i in range(len(expected_headers)):
                        check_col = first_category_col + i
                        if check_col < len(df.columns):
                            cell_value = str(df.iloc[row_idx, check_col]).strip()
                            # 检查这个单元格是否匹配任何期望的表头
                            matched_header = None
                            for expected_header in expected_headers:
                                if expected_header in cell_value and expected_header not in found_headers:
                                    matched_header = expected_header
                                    break
                            
                            if matched_header:
                                found_headers.append(matched_header)
                                header_cols[matched_header] = check_col
                                print(f"调试: ✓ 找到 {matched_header} 在第{check_col+1}列")
                            else:
                                print(f"调试: ✗ 第{check_col+1}列不匹配任何期望表头")
                    
                    # 如果找到了核心表头（前4个），确认这是产品工作表
                    core_headers = ['一级分类', '二级分类', '产品名称', '运输代码']
                    if all(header in found_headers for header in core_headers):
                        print(f"调试: ✓ 确认第{row_idx+1}行是完整的产品表头")
                        print(f"调试: 找到的所有列: {list(header_cols.keys())}")
                        header_row = row_idx
                        first_category_col = header_cols['一级分类']
                        second_category_col = header_cols['二级分类']
                        product_name_col = header_cols['产品名称']
                        shipping_code_col = header_cols['运输代码']
                        # 保存所有找到的列信息
                        all_header_cols = header_cols.copy()
                        break
                    else:
                        print(f"调试: ✗ 第{row_idx+1}行表头不完整，继续扫描...")
                        print(f"调试: 找到的列: {list(header_cols.keys())}")
                        first_category_col = None
            
            if not product_name_col or not shipping_code_col or header_row is None:
                print("警告: 未找到产品名称或运输代码列")
                return valid_products
            
            print(f"调试: 表头位置 - 行: {header_row+1}, 产品名称列: {product_name_col+1}, 运输代码列: {shipping_code_col+1}")
            if first_category_col is not None:
                print(f"调试: 一级分类列: {first_category_col+1}")
            if second_category_col is not None:
                print(f"调试: 二级分类列: {second_category_col+1}")
            
            # 从表头下一行开始顺序读取数据
            for row_idx in range(header_row + 1, len(df)):
                try:
                    product_name = str(df.iloc[row_idx, product_name_col]).strip()
                    shipping_code = str(df.iloc[row_idx, shipping_code_col]).strip()
                    
                    # 处理合并单元格，跳过空值
                    if product_name == 'nan' or not product_name or shipping_code == 'nan' or not shipping_code:
                        continue
                    
                    # 提取所有列的数据
                    product_data = {
                        'product_name': product_name,
                        'shipping_code': shipping_code
                    }
                    
                    # 提取分类信息
                    if first_category_col is not None:
                        first_category = str(df.iloc[row_idx, first_category_col]).strip()
                        product_data['first_category'] = first_category if first_category != 'nan' else ""
                    
                    if second_category_col is not None:
                        second_category = str(df.iloc[row_idx, second_category_col]).strip()
                        product_data['second_category'] = second_category if second_category != 'nan' else ""
                    
                    # 提取其他列的数据
                    for header_name, col_idx in all_header_cols.items():
                        if header_name not in ['一级分类', '二级分类', '产品名称', '运输代码']:
                            try:
                                cell_value = str(df.iloc[row_idx, col_idx]).strip()
                                product_data[header_name] = cell_value if cell_value != 'nan' else ""
                                print(f"调试: 提取列 {header_name}: {product_data[header_name]}")
                            except:
                                product_data[header_name] = ""
                                print(f"调试: 提取列 {header_name} 失败，设为空值")
                    
                    # 以product_name为主键存储
                    product_name = product_data.get('product_name', '')
                    if product_name:
                        valid_products[product_name] = product_data
                    
                    # 构建调试信息
                    debug_info = f"调试: 提取产品: {shipping_code} - {product_name}"
                    if 'first_category' in product_data:
                        debug_info += f" (一级分类: {product_data['first_category']}"
                    if 'second_category' in product_data:
                        debug_info += f", 二级分类: {product_data['second_category']}"
                    debug_info += ")"
                    print(debug_info)
                    
                except Exception as e:
                    print(f"调试: 读取第{row_idx+1}行数据时出错: {str(e)}")
                    continue
            
        except Exception as e:
            print(f"错误: 提取产品信息失败: {str(e)}")
        
        return valid_products
    
    def _parse_pricing_sheet(self, df, matched_product):
        """解析价格工作表的计费信息，返回三层嵌套结构：国家 -> 线路 -> 运费记录"""
        pricing_info = {}
        
        try:
            # 寻找表头行
            header_row = None
            country_col = None
            timeliness_col = None
            zone_col = None
            weight_col = None
            increment_col = None
            min_weight_col = None
            freight_col = None
            registration_col = None
            
            # 从第1行开始扫描，寻找表头
            for row_idx in range(min(10, len(df))):  # 最多扫描前10行
                for col_idx in range(len(df.columns)):
                    cell_value = str(df.iloc[row_idx, col_idx]).strip()
                    if cell_value == 'nan' or not cell_value:
                        continue
                    
                    # 寻找国家/地区列
                    if '国家' in cell_value or '地区' in cell_value or 'country' in cell_value.lower():
                        country_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找参考时效列
                    elif '时效' in cell_value or 'timeliness' in cell_value.lower():
                        timeliness_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找分区列
                    elif '分区' in cell_value or 'zone' in cell_value.lower():
                        zone_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找重量列
                    elif '重量' in cell_value or 'weight' in cell_value.lower():
                        weight_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找进位制列
                    elif '进位制' in cell_value or 'increment' in cell_value.lower():
                        increment_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找最低计费重列
                    elif '最低计费重' in cell_value or 'minimum' in cell_value.lower():
                        min_weight_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找运费列
                    elif '运费' in cell_value or 'freight' in cell_value.lower():
                        freight_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找挂号费列
                    elif '挂号费' in cell_value or 'registration' in cell_value.lower():
                        registration_col = col_idx
                        if header_row is None:
                            header_row = row_idx
            
            if header_row is None:
                print("警告: 未找到价格表头")
                return pricing_info
            
            
            # 从表头下一行开始读取数据，处理合并单元格
            current_country = ""
            current_timeliness = ""
            current_zone = ""
            
            for row_idx in range(header_row + 1, len(df)):
                try:
                    # 处理合并单元格：如果当前行有值就更新，否则使用之前的值
                    if country_col is not None:
                        country_value = str(df.iloc[row_idx, country_col]).strip()
                        if country_value != 'nan' and country_value:
                            current_country = country_value
                    
                    if timeliness_col is not None:
                        timeliness_value = str(df.iloc[row_idx, timeliness_col]).strip()
                        if timeliness_value != 'nan' and timeliness_value:
                            current_timeliness = timeliness_value
                    
                    if zone_col is not None:
                        zone_value = str(df.iloc[row_idx, zone_col]).strip()
                        if zone_value != 'nan' and zone_value:
                            current_zone = zone_value
                    
                    # 提取重量信息
                    weight = ""
                    if weight_col is not None:
                        weight = str(df.iloc[row_idx, weight_col]).strip()
                        if weight == 'nan':
                            weight = ""
                    
                    # 提取运费信息
                    freight = ""
                    if freight_col is not None:
                        freight = str(df.iloc[row_idx, freight_col]).strip()
                        if freight == 'nan':
                            freight = ""
                    
                    # 提取挂号费信息
                    registration = ""
                    if registration_col is not None:
                        registration = str(df.iloc[row_idx, registration_col]).strip()
                        if registration == 'nan':
                            registration = ""
                    
                    # 提取最低计费重信息
                    min_weight = ""
                    if min_weight_col is not None:
                        min_weight = str(df.iloc[row_idx, min_weight_col]).strip()
                        if min_weight == 'nan':
                            min_weight = ""
                    
                    # 提取进位制信息
                    increment = ""
                    if increment_col is not None:
                        increment = str(df.iloc[row_idx, increment_col]).strip()
                        if increment == 'nan':
                            increment = ""
                    
                    # 如果这一行有重量或运费数据，就添加到结果中
                    if weight or freight or registration:
                        pricing_record = {
                            'row': row_idx + 1,
                            'country': current_country,
                            'timeliness': current_timeliness,
                            'zone': current_zone,
                            'weight': weight,
                            'increment': increment,
                            'min_weight': min_weight,
                            'freight': freight,
                            'registration': registration,
                            'product_name': matched_product['product_name'],
                            'shipping_code': matched_product['shipping_code']
                        }
                        
                        # 按三层嵌套结构存储：国家 -> 线路 -> 运费记录
                        if current_country not in pricing_info:
                            pricing_info[current_country] = {}
                        
                        product_name = matched_product['product_name']
                        if product_name not in pricing_info[current_country]:
                            pricing_info[current_country][product_name] = []
                        
                        pricing_info[current_country][product_name].append(pricing_record)
                        # 注释掉详细的计费信息打印，避免输出太多
                        # print(f"调试: 提取计费信息 - 行{row_idx+1}: 国家={current_country}, 时效={current_timeliness}, 分区={current_zone}, 重量={weight}, 运费={freight}, 挂号费={registration}")
                
                except Exception as e:
                    continue
            
        except Exception as e:
            print(f"错误: 解析价格信息失败: {str(e)}")
        
        return pricing_info
    
    def _parse_tax_rates_table(self, excel_data):
        """解析欧洲国家对应税率表"""
        try:
            # 寻找税率表工作表
            tax_sheet_name = None
            for sheet_name in excel_data.keys():
                if '欧洲国家对应税率表' in sheet_name or '税率表' in sheet_name:
                    tax_sheet_name = sheet_name
                    break
            
            if not tax_sheet_name:
                print("调试: 未找到欧洲国家对应税率表")
                return
            
            print(f"调试: 找到税率表: {tax_sheet_name}")
            df = excel_data[tax_sheet_name]
            
            # 寻找表头行
            header_row = None
            country_col = None
            vat_rate_col = None
            service_fee_col = None
            
            # 从第0行第0列开始扫描，寻找表头
            for row_idx in range(len(df)):
                for col_idx in range(len(df.columns)):
                    cell_value = str(df.iloc[row_idx, col_idx]).strip()
                    if cell_value == 'nan' or not cell_value:
                        continue
                    
                    # 寻找国家列
                    if cell_value == '国家':
                        country_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找VAT税率列
                    elif cell_value == 'VAT税率':
                        vat_rate_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找服务费列
                    elif cell_value == '服务费':
                        service_fee_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                
                # 如果找到了表头，就停止扫描
                if header_row is not None:
                    break
            
            if header_row is None or country_col is None:
                print("警告: 未找到税率表表头")
                return
            
            print(f"调试: 税率表表头位置 - 行: {header_row+1}, 国家列: {country_col+1}, VAT税率列: {vat_rate_col+1 if vat_rate_col is not None else 'None'}, 服务费列: {service_fee_col+1 if service_fee_col is not None else 'None'}")
            
            # 从表头下一行开始读取数据
            for row_idx in range(header_row + 1, len(df)):
                try:
                    # 提取国家信息
                    country = str(df.iloc[row_idx, country_col]).strip()
                    if country == 'nan' or not country:
                        continue
                    
                    # 提取VAT税率
                    vat_rate = ""
                    if vat_rate_col is not None:
                        vat_rate = str(df.iloc[row_idx, vat_rate_col]).strip()
                        if vat_rate == 'nan':
                            vat_rate = ""
                    
                    # 提取服务费
                    service_fee = ""
                    if service_fee_col is not None:
                        service_fee = str(df.iloc[row_idx, service_fee_col]).strip()
                        if service_fee == 'nan':
                            service_fee = ""
                    
                    # 存储税率信息
                    if country:
                        self.tax_rates[country] = {
                            'vat_rate': vat_rate,
                            'service_fee': service_fee
                        }
                        # 注释掉详细的税率信息打印，避免输出太多
                        # print(f"调试: 提取税率信息 - 国家: {country}, VAT税率: {vat_rate}, 服务费: {service_fee}")
                
                except Exception as e:
                    continue
            
            print(f"信息: 解析到 {len(self.tax_rates)} 个国家的税率信息")
            
        except Exception as e:
            print(f"错误: 解析税率表失败: {str(e)}")

    def _parse_dimension_limits_table(self, excel_data):
        """解析包装尺寸限制表"""
        try:
            # 寻找尺寸限制表工作表
            dimension_sheet_name = None
            for sheet_name in excel_data.keys():
                if '尺寸表' in sheet_name:
                    dimension_sheet_name = sheet_name
                    break
            
            if not dimension_sheet_name:
                return
            
            df = excel_data[dimension_sheet_name]
            
            # 寻找表头行
            header_row = None
            country_col = None
            route_col = None  # 线路列
            min_size_col = None
            normal_size_col = None
            extra_charge_size_col = None
            
            # 从第0行开始扫描寻找表头
            for row_idx in range(len(df)):
                for col_idx in range(len(df.columns)):
                    cell_value = str(df.iloc[row_idx, col_idx]).strip()
                    if cell_value == 'nan' or not cell_value:
                        continue
                    
                    # 寻找国家/地区列
                    if cell_value == '国家/地区' or cell_value == '国家' or cell_value == '地区':
                        country_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找线路列
                    elif '线路' in cell_value or '产品' in cell_value or '渠道' in cell_value:
                        route_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找最小尺寸列
                    elif cell_value == '最小尺寸':
                        min_size_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找正常可发尺寸列
                    elif cell_value == '正常可发尺寸':
                        normal_size_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                    
                    # 寻找加收150RMB可发尺寸列
                    elif '加收' in cell_value and '150' in cell_value and '尺寸' in cell_value:
                        extra_charge_size_col = col_idx
                        if header_row is None:
                            header_row = row_idx
                
                # 如果找到了表头，就停止扫描
                if header_row is not None:
                    break
            
            if header_row is None or country_col is None:
                return
            
            print(f"调试: 尺寸表表头位置 - 行: {header_row+1}, 国家列: {country_col+1}, 线路列: {route_col+1 if route_col is not None else 'None'}")
            
            # 从表头下一行开始读取数据，处理合并单元格
            current_route = ""
            
            for row_idx in range(header_row + 1, len(df)):
                try:
                    # 处理合并单元格：如果当前行有值就更新，否则使用之前的值
                    if route_col is not None:
                        route_value = str(df.iloc[row_idx, route_col]).strip()
                        if route_value != 'nan' and route_value:
                            current_route = route_value
                    
                    country = str(df.iloc[row_idx, country_col]).strip()
                    if country == 'nan' or not country:
                        continue
                    
                    # 解析最小尺寸
                    min_size_text = ""
                    if min_size_col is not None:
                        min_size_text = str(df.iloc[row_idx, min_size_col]).strip()
                        if min_size_text == 'nan':
                            min_size_text = ""
                    
                    # 解析正常可发尺寸
                    normal_size_text = ""
                    if normal_size_col is not None:
                        normal_size_text = str(df.iloc[row_idx, normal_size_col]).strip()
                        if normal_size_text == 'nan':
                            normal_size_text = ""
                    
                    # 解析加收150RMB可发尺寸
                    extra_charge_size_text = ""
                    if extra_charge_size_col is not None:
                        extra_charge_size_text = str(df.iloc[row_idx, extra_charge_size_col]).strip()
                        if extra_charge_size_text == 'nan':
                            extra_charge_size_text = ""
                    
                    # 构建复合键：国家_线路
                    route_key = current_route if current_route else "默认线路"
                    composite_key = f"{country}_{route_key}"
                    
                    # 保存尺寸限制信息
                    self.dimension_limits[composite_key] = {
                        'country': country,
                        'route': route_key,
                        'min_size_text': min_size_text,
                        'normal_size_text': normal_size_text,
                        'extra_charge_size_text': extra_charge_size_text
                    }
                    
                except Exception as e:
                    continue
            
            print(f"信息: 解析到 {len(self.dimension_limits)} 个国家和线路的尺寸限制信息")
            
        except Exception as e:
            print(f"错误: 解析尺寸限制表失败: {str(e)}")
    
    def _find_matching_product(self, sheet_name, valid_products):
        """查找与工作表名称匹配的产品"""
        # 清理工作表名称，去掉空格
        clean_sheet_name = sheet_name.strip()
        
        for product_name, product_info in valid_products.items():
            # 清理产品名称，去掉空格
            clean_product_name = product_name.strip()
                         
            # 多种匹配方式
            if clean_product_name and clean_product_name in clean_sheet_name:
                return product_info
            elif clean_sheet_name and clean_sheet_name in clean_product_name:
                return product_info
            elif clean_product_name and clean_sheet_name and clean_product_name == clean_sheet_name:
                return product_info
        
        return None
    
    def _is_valid_price_sheet(self, sheet_name, valid_products):
        """检查工作表是否对应有效产品"""
        return self._find_matching_product(sheet_name, valid_products) is not None
    
    def get_eff_date(self):
        """获取当前资费生效时间"""
        return self._eff_date
    
    def get_version_no(self):
        """获取当前资费版本号"""
        return self._version_no
    
    def get_parsing_summary(self):
        """获取解析摘要信息"""
        summary = {
            'current_file': self.current_tariff_file['file_name'] if self.current_tariff_file else None,
            'effective_date': self._eff_date,
            'version': self._version_no,
            'total_sheets': len(self.pricing_data),
            'total_products': 0,
            'total_pricing_records': 0,
            'total_tax_rates': len(self.tax_rates),
            'sheets_info': []
        }
        
        for sheet_key, data in self.pricing_data.items():
            sheet_info = {
                'sheet_name': data['sheet_name'],
                'shape': data['shape'],
                'columns': data['columns'][:5],  # 只显示前5列
                'has_valid_products': 'matched_product' in data or 'valid_products' in data,
                'pricing_records': 0
            }
            
            if 'matched_product' in data:
                sheet_info['matched_product'] = data['matched_product']['product_name']
                sheet_info['shipping_code'] = data['matched_product']['shipping_code']
                summary['total_products'] += 1
                
                if 'pricing_info' in data:
                    # 计算所有国家的定价记录总数（现在是三层嵌套结构）
                    total_records = 0
                    for country, routes in data['pricing_info'].items():
                        for route_name, records in routes.items():
                            total_records += len(records)
                    sheet_info['pricing_records'] = total_records
                    summary['total_pricing_records'] += total_records
                    
            elif 'valid_products' in data:
                sheet_info['product_count'] = len(data['valid_products'])
                sheet_info['products'] = list(data['valid_products'].keys())
                summary['total_products'] += len(data['valid_products'])
                
            summary['sheets_info'].append(sheet_info)
        
        return summary
    
    def get_product_by_code(self, shipping_code):
        """根据运输代码获取产品信息"""
        for sheet_key, data in self.pricing_data.items():
            if 'matched_product' in data and data['matched_product']['shipping_code'] == shipping_code:
                return data['matched_product']
        return None
    
    def get_pricing_by_code(self, shipping_code):
        """根据运输代码获取计费信息，返回三层嵌套结构：国家 -> 线路 -> 运费记录"""
        for sheet_key, data in self.pricing_data.items():
            if 'matched_product' in data and data['matched_product']['shipping_code'] == shipping_code:
                pricing_info = data.get('pricing_info', {})
                # 过滤掉无效的国家名称
                filtered_pricing = {}
                for country, routes in pricing_info.items():
                    if country and country not in ['运输代码:', '生效日期', '尺寸表', '欧洲国家对应税率表', '禁运品清单', '产品品牌表', '申报价值注意事项']:
                        filtered_pricing[country] = routes
                return filtered_pricing
        return {}
    
    def get_pricing_by_destination(self, destination_country, shipping_mode=None, cargo_type=None, 
                                 length=None, width=None, height=None, weight=None, 
                                 destination_state=None, destination_postal_code=None):
        """
        根据目的地国家和其他可选参数获取计费和算费信息
        
        Args:
            destination_country (str): 目的地国家（必选）
            shipping_mode (str, optional): 快运模式（经济、快速、标准）
            cargo_type (str, optional): 走货属性（带电、服装、化妆品等）
            length (float, optional): 长度 (cm)
            width (float, optional): 宽度 (cm)
            height (float, optional): 高度 (cm)
            weight (float, optional): 重量 (kg)
            destination_state (str, optional): 目的地州
            destination_postal_code (str, optional): 目的地邮编
            
        Returns:
            dict: 包含产品信息和计费信息的响应
                {
                    'success': bool,
                    'message': str,
                    'data': {
                        'query_params': dict,
                        'available_products': dict,  # 以product_name为主键的map
                        'products': dict,  # 过滤后的产品，以product_name为主键的map
                        'summary': dict
                    }
                }
        """
        try:
            # 记录输入参数
            frappe.logger().error(f"=== get_pricing_by_destination 方法调用 ===")
            frappe.logger().error(f"输入参数:")
            frappe.logger().error(f"  destination_country: {destination_country}")
            frappe.logger().error(f"  shipping_mode: {shipping_mode}")
            frappe.logger().error(f"  cargo_type: {cargo_type}")
            frappe.logger().error(f"  length: {length}")
            frappe.logger().error(f"  width: {width}")
            frappe.logger().error(f"  height: {height}")
            frappe.logger().error(f"  weight: {weight}")
            frappe.logger().error(f"  destination_state: {destination_state}")
            frappe.logger().error(f"  destination_postal_code: {destination_postal_code}")
            frappe.logger().error(f"==========================================")
            
            # 参数验证
            if not destination_country:
                return {
                    'success': False,
                    'message': '目的地国家是必选参数',
                    'data': None
                }
            
            # 检查是否有产品数据，如果为空则尝试从pkl文件加载
            if not self._global_pricing_data or not self._global_pricing_info:
                frappe.logger().error("信息: 数据为空，尝试从pkl文件加载...")
                if self._load_from_pkl_files():
                    frappe.logger().error("信息: 从pkl文件加载数据成功")
                    # 更新全局缓存
                    self._update_global_cache()
                else:
                    return {
                        'success': False,
                        'message': '未找到任何产品信息，且无法从pkl文件加载',
                        'data': None
                    }
            
            # 第一步：根据国家从pricing_info中获取该国家下的所有线路
            frappe.logger().error(f"第一步: 查找国家 '{destination_country}' 的线路信息...")
            frappe.logger().error(f"可用的国家列表: {list(self._global_pricing_info.keys())[:5]}...")  # 只显示前5个国家
            
            country_routes = {}
            for country in self._global_pricing_info.keys():
                if (destination_country == country or 
                    destination_country.strip() == country.strip() or
                    destination_country.lower() == country.lower()):
                    country_routes = self._global_pricing_info[country]
                    frappe.logger().error(f"找到匹配国家: '{country}', 线路数量: {len(country_routes)}")
                    break
            
            if not country_routes:
                return {
                    'success': False,
                    'message': f'未找到目的地国家 "{destination_country}" 的线路信息',
                    'data': {
                        'query_params': {
                            'destination_country': destination_country,
                            'shipping_mode': shipping_mode,
                            'cargo_type': cargo_type,
                            'length': length,
                            'width': width,
                            'height': height,
                            'weight': weight,
                            'destination_state': destination_state,
                            'destination_postal_code': destination_postal_code
                        },
                        'available_products': {},
                        'products': {},
                        'summary': {
                            'total_available_products': 0,
                            'total_filtered_products': 0,
                            'countries_supported': []
                        }
                    }
                }
            
            # 第二步：直接基于线路进行参数过滤
            frappe.logger().error(f"第二步: 基于线路进行参数过滤...")
            frappe.logger().error(f"该国家的线路: {list(country_routes.keys())}")
            
            # 如果没有其他筛选条件，返回所有支持该国家的线路
            if not shipping_mode and not cargo_type:
                frappe.logger().error("没有筛选条件，返回所有线路")
                filtered_products = country_routes
            else:
                frappe.logger().error(f"筛选条件: shipping_mode={shipping_mode}, cargo_type={cargo_type}")
                filtered_products = {}
                
                for product_name, pricing_records in country_routes.items():
                    frappe.logger().error(f"检查线路: {product_name}")
                    
                    # 检查快运模式匹配
                    if shipping_mode:
                        matched = False
                        if shipping_mode == 'express':
                            # 快速匹配：包含"快速"、"express"、"dhl"、"fedex"等
                            matched = ('快速' in product_name or 
                                     'express' in product_name.lower() or
                                     'dhl' in product_name.lower() or
                                     'fedex' in product_name.lower() or
                                     'ups' in product_name.lower())
                        elif shipping_mode == 'economy':
                            # 经济匹配：包含"经济"、"economy"、"eub"等
                            matched = ('经济' in product_name or 
                                     'economy' in product_name.lower() or
                                     'eub' in product_name.lower())
                        elif shipping_mode == 'post':
                            # 邮政小包匹配：包含"邮政"、"post"、"小包"等
                            matched = ('邮政' in product_name or 
                                     'post' in product_name.lower() or
                                     '小包' in product_name or
                                     '挂号' in product_name)
                        
                        if not matched:
                            frappe.logger().error(f"  快运模式不匹配: {product_name} 不匹配 {shipping_mode}")
                            continue
                        else:
                            frappe.logger().error(f"  快运模式匹配: {product_name} 匹配 {shipping_mode}")
                    
                    # 检查走货属性匹配（这里需要从pricing_data中获取产品信息）
                    if cargo_type:
                        # 将英文cargo_type转换为中文关键词
                        cargo_keywords = []
                        if cargo_type == 'general':
                            cargo_keywords = ['普货', '一般', '普通']
                        elif cargo_type == 'battery':
                            cargo_keywords = ['带电', '电池', 'battery']
                        elif cargo_type == 'specials':
                            cargo_keywords = ['特殊', '其他', 'special']
                        
                        # 从pricing_data中查找产品信息
                        product_info = None
                        for sheet_key, data in self._global_pricing_data.items():
                            if 'matched_product' in data and data['matched_product'].get('product_name') == product_name:
                                product_info = data['matched_product']
                                break
                        
                        if product_info:
                            product_attributes = product_info.get('走货属性', '').lower()
                            matched = False
                            for keyword in cargo_keywords:
                                if keyword.lower() in product_attributes:
                                    matched = True
                                    break
                            
                            if not matched:
                                frappe.logger().error(f"  走货属性不匹配: {product_attributes} 不包含 {cargo_keywords}")
                                continue
                            else:
                                frappe.logger().error(f"  走货属性匹配: {product_attributes} 包含 {cargo_keywords}")
                        else:
                            # 如果找不到产品信息，尝试从产品名称中匹配
                            matched = False
                            for keyword in cargo_keywords:
                                if keyword in product_name:
                                    matched = True
                                    break
                            
                            if not matched:
                                frappe.logger().error(f"  走货属性不匹配: {product_name} 不包含 {cargo_keywords}")
                                continue
                            else:
                                frappe.logger().error(f"  走货属性匹配: {product_name} 包含 {cargo_keywords}")
                    
                    filtered_products[product_name] = pricing_records
                    frappe.logger().error(f"  线路通过筛选: {product_name}")
            
            frappe.logger().error(f"筛选后的线路数量: {len(filtered_products)}")
            
            if not filtered_products:
                return {
                    'success': False,
                    'message': f'未找到支持目的地国家 "{destination_country}" 的线路',
                    'data': {
                        'query_params': {
                            'destination_country': destination_country,
                            'shipping_mode': shipping_mode,
                            'cargo_type': cargo_type,
                            'length': length,
                            'width': width,
                            'height': height,
                            'weight': weight,
                            'destination_state': destination_state,
                            'destination_postal_code': destination_postal_code
                        },
                        'available_products': {},
                        'products': {},
                        'summary': {
                            'total_available_products': 0,
                            'total_filtered_products': 0,
                            'countries_supported': list(set())
                        }
                    }
                }
            
            # 第三步：构建响应数据
            frappe.logger().error(f"第三步: 构建响应数据...")
            
            # 构建响应数据
            response_data = {
                'query_params': {
                    'destination_country': destination_country,
                    'shipping_mode': shipping_mode,
                    'cargo_type': cargo_type,
                    'length': length,
                    'width': width,
                    'height': height,
                    'weight': weight,
                    'destination_state': destination_state,
                    'destination_postal_code': destination_postal_code
                },
                'available_products': country_routes,
                'products': {},
                'summary': {
                    'total_available_products': len(country_routes),
                    'total_filtered_products': len(filtered_products),
                    'countries_supported': [destination_country]
                }
            }
            
            # 构建新的三层嵌套pricing_data结构
            pricing_data = {destination_country: {}}
            
            # 为每个产品添加计费信息和相关计算
            for product_name, pricing_records in filtered_products.items():
                # 从pricing_data中获取产品信息
                product_info = None
                for sheet_key, data in self._global_pricing_data.items():
                    if 'matched_product' in data and data['matched_product'].get('product_name') == product_name:
                        product_info = data['matched_product']
                        break
                
                # 构建三层嵌套结构：国家 -> 线路 -> 运费信息
                pricing_data[destination_country][product_name] = pricing_records
                
                # 构建产品结果
                product_result = {
                    'product_info': product_info or {},
                    'pricing_records': pricing_records
                }
                
                # 添加到响应数据中
                response_data['products'][product_name] = product_result
                
                # 计算计费重量（如果提供了尺寸和重量参数）
                billing_weight = None
                volumetric_weight = None
                if length and width and height and weight:
                    try:
                        volumetric_weight = self.calculate_volumetric_weight(
                            length, width, height, destination_country, shipping_code
                        )
                        billing_weight = self.calculate_billing_weight(
                            weight, length, width, height, destination_country, shipping_code
                        )
                    except Exception as e:
                        print(f"计算重量时出错: {e}")
                
                # 获取税率信息
                tax_info = self.get_tax_rate_by_country(destination_country)
                
                # 获取尺寸限制信息
                dimension_info = self.get_dimension_limits_by_country(destination_country)
                
                # 构建产品结果
                product_result = {
                    'product_info': product_info,
                    'billing_calculation': {
                        'actual_weight': weight,
                        'volumetric_weight': volumetric_weight,
                        'billing_weight': billing_weight,
                        'dimensions': {
                            'length': length,
                            'width': width,
                            'height': height
                        }
                    },
                    'tax_info': tax_info,
                    'dimension_info': dimension_info
                }
                
                # 更新响应数据中的产品信息
                response_data['products'][product_name] = product_result
            
            # 将构建好的pricing_data添加到响应数据中
            response_data['pricing_data'] = pricing_data
            
            frappe.logger().error(f"=== 方法执行完成 ===")
            frappe.logger().error(f"最终结果: 成功获取 {len(filtered_products)} 个线路")
            frappe.logger().error(f"可用线路: {len(country_routes)} 个")
            frappe.logger().error(f"筛选后线路: {len(filtered_products)} 个")
            frappe.logger().error(f"==========================================")
            
            return {
                'success': True,
                'message': f'成功获取 {len(filtered_products)} 个产品的计费信息',
                'data': response_data
            }
            
        except Exception as e:
            return {
                'success': False,
                'message': f'获取计费信息失败: {str(e)}',
                'data': None
            }
    

    
    def get_all_products(self):
        """获取所有产品信息，返回以product_name为主键的map结构"""
        products = {}
        for sheet_key, data in self.pricing_data.items():
            if 'matched_product' in data:
                product = data['matched_product']
                product_name = product.get('product_name', '')
                if product_name:
                    products[product_name] = product
        return products
    
    def search_products(self, keyword):
        """根据关键词搜索产品，返回以product_name为主键的map结构"""
        results = {}
        keyword = keyword.lower()
        
        for sheet_key, data in self.pricing_data.items():
            if 'matched_product' in data:
                product = data['matched_product']
                if (keyword in product['product_name'].lower() or 
                    keyword in product['shipping_code'].lower() or
                    keyword in product.get('first_category', '').lower() or
                    keyword in product.get('second_category', '').lower()):
                    product_name = product.get('product_name', '')
                    if product_name:
                        results[product_name] = product
        
        return results
    
    def get_tax_rate_by_country(self, country):
        """根据国家获取税率信息"""
        return self.tax_rates.get(country, None)
    
    def get_all_tax_rates(self):
        """获取所有国家的税率信息"""
        return self.tax_rates
    
    def get_countries_with_tax_rates(self):
        """获取所有有税率信息的国家列表"""
        return list(self.tax_rates.keys())
    
    def get_countries_with_pricing(self):
        """获取有定价信息的国家列表"""
        countries = set()
        for sheet_key, data in self.pricing_data.items():
            if 'pricing_info' in data:
                pricing_info = data['pricing_info']
                for country in pricing_info.keys():
                    if country and country not in ['运输代码:', '生效日期', '尺寸表', '欧洲国家对应税率表', '禁运品清单', '产品品牌表', '申报价值注意事项']:
                        countries.add(country)
        return sorted(list(countries))

    def get_dimension_limits_by_country(self, country, route=None):
        """根据国家和线路获取尺寸限制信息"""
        if route:
            # 精确匹配：国家_线路
            composite_key = f"{country}_{route}"
            return self.dimension_limits.get(composite_key, None)
        else:
            # 只按国家查找，返回第一个匹配的线路
            for key, value in self.dimension_limits.items():
                if value.get('country') == country:
                    return value
            return None

    def get_dimension_limits_by_country_and_route(self, country, route):
        """根据国家和线路精确获取尺寸限制信息"""
        composite_key = f"{country}_{route}"
        return self.dimension_limits.get(composite_key, None)

    def get_all_dimension_limits(self):
        """获取所有尺寸限制信息"""
        return self.dimension_limits

    def get_countries_with_dimension_limits(self):
        """获取所有有尺寸限制信息的国家列表"""
        countries = set()
        for value in self.dimension_limits.values():
            countries.add(value.get('country', ''))
        return list(countries)

    def get_routes_for_country(self, country):
        """获取指定国家的所有线路列表"""
        routes = []
        for key, value in self.dimension_limits.items():
            if value.get('country') == country:
                routes.append(value.get('route', ''))
        return routes

    def get_all_country_route_combinations(self):
        """获取所有国家和线路的组合"""
        combinations = []
        for key, value in self.dimension_limits.items():
            combinations.append({
                'country': value.get('country', ''),
                'route': value.get('route', ''),
                'key': key
            })
        return combinations
    
    
    def get_billing_weight_rule_for_country(self, shipping_code, country):
        """根据运输代码和国家获取计费重量规则"""
        # 备注规则解析已移除，返回None使用默认规则
        return None
    
    def calculate_volumetric_weight(self, length, width, height, country, shipping_code):
        """计算体积重量"""
        rule = self.get_billing_weight_rule_for_country(shipping_code, country)
        if not rule:
            # 使用默认公式
            return (length * width * height) / 8000
        
        # 使用规则中的分母
        divisor = rule.get('volumetric_divisor', 8000)
        return (length * width * height) / divisor
    
    def calculate_billing_weight(self, actual_weight, length, width, height, country, shipping_code):
        """计算计费重量"""
        rule = self.get_billing_weight_rule_for_country(shipping_code, country)
        if not rule:
            # 使用默认规则：取较大者，分母8000
            volumetric_weight = (length * width * height) / 8000
            return max(actual_weight, volumetric_weight)
        
        # 检查是否只按实际重量计费（不计抛）
        if rule.get('is_actual_weight_only', False):
            return actual_weight
        
        # 计算体积重量，使用规则中的分母
        volumetric_divisor = rule.get('volumetric_divisor', 8000)
        volumetric_weight = (length * width * height) / volumetric_divisor
        
        # 统一规则：取实际重量和体积重量的较大者
        return max(actual_weight, volumetric_weight)
    
    def check_dimension(self, country, length, width, height, route=None):
        """
        检查包裹尺寸是否符合指定国家和线路的尺寸限制
        
        Args:
            country (str): 国家名称
            length (float): 长度 (cm)
            width (float): 宽度 (cm) 
            height (float): 高度 (cm)
            route (str, optional): 线路名称，如果提供则精确匹配
            
        Returns:
            dict: 校验结果
                {
                    'allowed': bool,  # 是否允许发送
                    'status': str,    # 状态描述
                    'reason': str,    # 原因说明
                    'normal_violations': list,  # 正常尺寸违规项
                    'extra_charge_required': bool,  # 是否需要额外费用
                    'country': str,   # 国家
                    'route': str      # 线路
                }
        """
        try:
            # 获取该国家和线路的尺寸限制信息
            dim_info = self.get_dimension_limits_by_country(country, route)
            if not dim_info:
                if route:
                    return {
                        'allowed': True,
                        'status': 'no_limits',
                        'reason': f'国家 {country} 线路 {route} 没有尺寸限制，允许发送',
                        'normal_violations': [],
                        'extra_charge_required': False,
                        'country': country,
                        'route': route
                    }
                else:
                    return {
                        'allowed': True,
                        'status': 'no_limits',
                        'reason': f'国家 {country} 没有尺寸限制，允许发送',
                        'normal_violations': [],
                        'extra_charge_required': False,
                        'country': country,
                        'route': route or '默认'
                    }
            
            # 解析尺寸限制
            min_size = self._parse_dimension_text(dim_info.get('min_size_text', ''))
            normal_size = self._parse_dimension_text(dim_info.get('normal_size_text', ''))
            extra_charge_size = self._parse_dimension_text(dim_info.get('extra_charge_size_text', ''))
            
            # 获取国家和线路信息
            actual_country = dim_info.get('country', country)
            actual_route = dim_info.get('route', route or '默认')
            
            # 第一步：检查是否小于最小尺寸
            if min_size and self._is_smaller_than_minimum(length, width, height, min_size):
                return {
                    'allowed': False,
                    'status': 'too_small',
                    'reason': '包裹尺寸小于最小尺寸要求，不允许发送',
                    'normal_violations': [],
                    'extra_charge_required': False,
                    'country': actual_country,
                    'route': actual_route
                }
            
            # 第二步：检查正常发送尺寸
            normal_violations = []
            if normal_size:
                normal_violations = self._check_dimension_violations(length, width, height, normal_size)
            
            # 如果正常尺寸检查通过
            if not normal_violations:
                return {
                    'allowed': True,
                    'status': 'normal',
                    'reason': '包裹尺寸符合正常发送要求',
                    'normal_violations': [],
                    'extra_charge_required': False,
                    'country': actual_country,
                    'route': actual_route
                }
            
            # 第三步：检查超大尺寸
            if extra_charge_size:
                extra_violations = self._check_dimension_violations(length, width, height, extra_charge_size)
                
                # 如果超大尺寸也不满足
                if extra_violations:
                    return {
                        'allowed': False,
                        'status': 'exceeds_all_limits',
                        'reason': '包裹尺寸不满足正常发送和超大尺寸规则，不允许发送',
                        'normal_violations': normal_violations,
                        'extra_charge_required': False,
                        'country': actual_country,
                        'route': actual_route
                    }
                else:
                    # 超大尺寸满足，但需要额外费用
                    return {
                        'allowed': True,
                        'status': 'extra_charge',
                        'reason': f'常规规格不能发送（{", ".join(normal_violations)}），但通过增加费用可以超大尺寸发送',
                        'normal_violations': normal_violations,
                        'extra_charge_required': True,
                        'country': actual_country,
                        'route': actual_route
                    }
            else:
                # 没有超大尺寸选项
                return {
                    'allowed': False,
                    'status': 'exceeds_normal',
                    'reason': f'包裹尺寸超过正常发送要求（{", ".join(normal_violations)}），不允许发送',
                    'normal_violations': normal_violations,
                    'extra_charge_required': False,
                    'country': actual_country,
                    'route': actual_route
                }
                
        except Exception as e:
            return {
                'allowed': False,
                'status': 'error',
                'reason': f'尺寸校验过程中发生错误: {str(e)}',
                'normal_violations': [],
                'extra_charge_required': False,
                'country': country,
                'route': route or '未知'
            }
    
    def _parse_dimension_text(self, dimension_text):
        """
        解析尺寸文本，提取尺寸限制信息
        
        Args:
            dimension_text (str): 尺寸文本，如 "10*15cm", "最长边≤60cm", "L+W+H≤90cm"
            
        Returns:
            dict: 解析后的尺寸限制信息
                {
                    'type': str,  # 'lwh' | 'max_side' | 'sum_three' | 'unknown'
                    'length': float,  # 长度限制
                    'width': float,   # 宽度限制  
                    'height': float,  # 高度限制
                    'max_single_side': float,  # 最长边限制
                    'max_three_sides': float,  # 三边和限制
                    'raw_text': str   # 原始文本
                }
        """
        if not dimension_text or dimension_text == 'nan':
            return None
            
        dimension_text = str(dimension_text).strip()
        
        # 模式1: L*W*H格式，如 "10*15*20cm", "L<100*W<80*H<60cm"
        lwh_pattern = r'(\d+(?:\.\d+)?)\s*\*\s*(\d+(?:\.\d+)?)\s*\*\s*(\d+(?:\.\d+)?)\s*cm'
        match = re.search(lwh_pattern, dimension_text)
        if match:
            return {
                'type': 'lwh',
                'length': float(match.group(1)),
                'width': float(match.group(2)),
                'height': float(match.group(3)),
                'max_single_side': None,
                'max_three_sides': None,
                'raw_text': dimension_text
            }
        
        # 模式2: 最长边限制，如 "最长边≤60cm", "单边不超60cm"
        max_side_pattern = r'(?:最长边|单边不超|最长边不超).*?≤?\s*(\d+(?:\.\d+)?)\s*cm'
        match = re.search(max_side_pattern, dimension_text)
        if match:
            return {
                'type': 'max_side',
                'length': None,
                'width': None,
                'height': None,
                'max_single_side': float(match.group(1)),
                'max_three_sides': None,
                'raw_text': dimension_text
            }
        
        # 模式3: 三边和限制，如 "L+W+H≤90cm", "长+宽+高≤90cm", "L+W+H<90cm"
        sum_three_pattern = r'(?:L\+W\+H|长\+宽\+高|三边和).*?[≤<]\s*(\d+(?:\.\d+)?)\s*cm'
        match = re.search(sum_three_pattern, dimension_text)
        if match:
            return {
                'type': 'sum_three',
                'length': None,
                'width': None,
                'height': None,
                'max_single_side': None,
                'max_three_sides': float(match.group(1)),
                'raw_text': dimension_text
            }
        
        # 模式4: 简单数字+cm，如 "60cm" (假设为最长边限制)
        simple_pattern = r'(\d+(?:\.\d+)?)\s*cm'
        match = re.search(simple_pattern, dimension_text)
        if match:
            return {
                'type': 'max_side',
                'length': None,
                'width': None,
                'height': None,
                'max_single_side': float(match.group(1)),
                'max_three_sides': None,
                'raw_text': dimension_text
            }
        
        # 无法解析
        return {
            'type': 'unknown',
            'length': None,
            'width': None,
            'height': None,
            'max_single_side': None,
            'max_three_sides': None,
            'raw_text': dimension_text
        }
    
    def _is_smaller_than_minimum(self, length, width, height, min_size):
        """检查是否小于最小尺寸"""
        if not min_size or min_size['type'] == 'unknown':
            return False
            
        if min_size['type'] == 'lwh':
            return (length < min_size['length'] and 
                   width < min_size['width'] and 
                   height < min_size['height'])
        elif min_size['type'] == 'max_side':
            max_side = max(length, width, height)
            return max_side < min_size['max_single_side']
        elif min_size['type'] == 'sum_three':
            sum_three = length + width + height
            return sum_three < min_size['max_three_sides']
        
        return False
    
    def _check_dimension_violations(self, length, width, height, size_limit):
        """检查尺寸违规项"""
        violations = []
        
        if not size_limit or size_limit['type'] == 'unknown':
            return violations
            
        if size_limit['type'] == 'lwh':
            if length > size_limit['length']:
                violations.append(f"长度{length}cm超过限制{size_limit['length']}cm")
            if width > size_limit['width']:
                violations.append(f"宽度{width}cm超过限制{size_limit['width']}cm")
            if height > size_limit['height']:
                violations.append(f"高度{height}cm超过限制{size_limit['height']}cm")
                
        elif size_limit['type'] == 'max_side':
            max_side = max(length, width, height)
            if max_side > size_limit['max_single_side']:
                violations.append(f"最长边{max_side}cm超过限制{size_limit['max_single_side']}cm")
                
        elif size_limit['type'] == 'sum_three':
            sum_three = length + width + height
            if sum_three > size_limit['max_three_sides']:
                violations.append(f"三边和{sum_three}cm超过限制{size_limit['max_three_sides']}cm")
        
        return violations
    
    def validate_data_integrity(self):
        """验证数据完整性"""
        issues = []
        
        # 检查是否有产品数据
        if not self.pricing_data:
            issues.append("没有解析到任何数据")
            return issues
        
        # 检查产品数据完整性
        for sheet_key, data in self.pricing_data.items():
            if 'matched_product' in data:
                product = data['matched_product']
                
                # 检查必需字段
                if not product.get('product_name'):
                    issues.append(f"工作表 {data['sheet_name']} 缺少产品名称")
                if not product.get('shipping_code'):
                    issues.append(f"工作表 {data['sheet_name']} 缺少运输代码")
                
                # 检查计费信息
                if 'pricing_info' in data:
                    pricing_info = data['pricing_info']
                    if not pricing_info:
                        issues.append(f"工作表 {data['sheet_name']} 没有计费信息")
                    else:
                        # 检查计费信息完整性 - 现在是三层嵌套结构
                        record_count = 0
                        for country, routes in pricing_info.items():
                            for route_name, records in routes.items():
                                for i, pricing in enumerate(records):
                                    record_count += 1
                                    if not pricing.get('weight') and not pricing.get('freight'):
                                        issues.append(f"工作表 {data['sheet_name']} 国家{country} 线路{route_name} 第{i+1}条计费信息缺少重量和运费")
        
        return issues
    
    def export_to_json(self, output_path=None):
        """导出数据为JSON格式"""
        if not output_path:
            output_path = self.tariff_dir / f"pricing_data_{datetime.now().strftime('%Y%m%d_%H%M%S')}.json"
        
        try:
            # 构建有用的三层嵌套pricing_data结构
            useful_pricing_data = {}
            for sheet_key, data in self.pricing_data.items():
                if 'pricing_info' in data and 'matched_product' in data:
                    product_name = data['matched_product']['product_name']
                    pricing_info = data['pricing_info']
                    
                    # 将数据重新组织为三层嵌套结构：国家 -> 线路 -> 运费记录
                    for country, routes in pricing_info.items():
                        if country and country not in ['运输代码:', '生效日期', '尺寸表', '欧洲国家对应税率表', '禁运品清单', '产品品牌表', '申报价值注意事项']:
                            if country not in useful_pricing_data:
                                useful_pricing_data[country] = {}
                            useful_pricing_data[country][product_name] = routes
            
            # 准备导出数据
            export_data = {
                'metadata': {
                    'export_time': datetime.now().isoformat(),
                    'current_file': self.current_tariff_file['file_name'] if self.current_tariff_file else None,
                    'effective_date': self._eff_date,
                    'version': self._version_no,
                    'total_sheets': len(self.pricing_data),
                    'total_products': len(self.get_all_products()),
                    'total_pricing_records': sum(sum(len(records) for records in routes.values()) for routes in useful_pricing_data.values()),
                    'total_tax_rates': len(self.tax_rates)
                },
                'products': self.get_all_products(),
                'tax_rates': self.tax_rates,
                'dimension_limits': self.dimension_limits,
                'pricing_data': useful_pricing_data
            }
            
            # 写入文件
            with open(output_path, 'w', encoding='utf-8') as f:
                json.dump(export_data, f, ensure_ascii=False, indent=2)
            
            print(f"数据已导出到: {output_path}")
            return output_path
            
        except Exception as e:
            print(f"导出数据失败: {str(e)}")
            return None


